<?php
require_once(Kohana::find_file('vendor','phprpc/phprpc_client'));
/**
 * Created by PhpStorm.
 * User: locove
 * Date: 15-5-21
 * Time: 上午11:35
 */
class storsearch {
    public static $lists = array();
    public $page = null;
    public $download = null;
    public static $num = 0;

    public function doCondition() {
        $sql = storsearch::basicSql($_GET, $this->download);

        if(empty($this->download)) {
            $num = storsearch::getDatas($sql, 'num', NULL);

            return storsearch::$num = isset($num[0]) && $num[0] ? $num[0] : 0;
        }

        storsearch::doAction($sql, $_GET);
    }

    public function getLimitLists () {
        $sql = storsearch::basicSql($_GET, 1);
        $sql .= " LIMIT ".$this->page->items_per_page." OFFSET ".$this->page->_offset;

        storsearch::doAction($sql, $_GET);
    }

    public function doAction($sql, $_GET = NULL) {
        $places = storsearch::getDatas($sql, 'place');

        if(empty($places)) {
            storsearch::$lists = array();
            return;
        }

        $skus = array_keys($places);

        /*$sql = DB::select('cnname.name',DB::expr("upper(sku_cnname.sku) as sku"))
            ->from('sku_cnname')
            ->join('cnname')
            ->on('sku_cnname.attr_id', '=', 'cnname.id')
            ->where('sku', 'IN', $skus)
            ->compile('newerp');*/

        $sql = "SELECT b.name,UPPER(a.sku) as sku
                FROM skusystem_sku_cnname as a
                LEFT JOIN skusystem_cnname as b
                ON a.attr_id=b.id
                WHERE a.sku IN ('".join("','",$skus)."')";

        $skus_names = storsearch::getDatas($sql, 'name');

        /*$sql = DB::select(DB::expr('upper(sku) as sku'),'status_id')
            ->from('status_skuss')
            ->where('sku', 'IN', $skus)
            ->compile('newerp');*/

        $sql = "SELECT UPPER(sku) as sku ,status_id
                FROM skusystem_status_skuss
                WHERE sku in ('".join("','",$skus)."')";

        $skus_status = storsearch::getDatas($sql, 'status_id');

        $sql = DB::select('id','status')->from('skusystem_statuss')->compile('newerp');

        $status = storsearch::getDatas($sql, 'status', 'id');

        $stocks = storsearch::countStock($skus, isset($_GET['end'])?$_GET['end']:NULL);

        foreach ($skus as $k=>$sku) {
            storsearch::$lists[$k]['sku']      = $sku;
            storsearch::$lists[$k]['name']     = isset($skus_names[$sku])?$skus_names[$sku]:'';
            storsearch::$lists[$k]['storage']  = isset($places[$sku])?$places[$sku]:'';
            storsearch::$lists[$k]['stock']    = isset($stocks[$sku])?$stocks[$sku]:0;
            storsearch::$lists[$k]['status']   = (isset($skus_status[$sku])&&isset($status[$skus_status[$sku]]))
                ?$status[$skus_status[$sku]]:'';
        }
    }

    public static function basicSql($_GET, $c = NULL) {
        $cols = !empty($c)?"upper(`ps`.`sku`) `sku` , `p`.`place`":"count(*) as num";
        $sql = "SELECT
             {$cols}
        FROM `skusystem_places` `p`
        JOIN `skusystem_place_skus` `ps`
        ON `p`.`id` = `ps`.`place_id`
        WHERE `ps`.`isavailable` = 1 ";

        $pre = empty($_GET['pre'])?'':strtoupper(trim($_GET['pre']));
        empty($_GET['stor1']) && $sto1 = TRUE && empty($_GET['stor2']) && $sto2 = TRUE;

        $sri = 'AND `p`.`place` ';
        if(!empty($_GET['pre']) || !isset($sto2)) {
            $sql .= $sri;
            if(isset($sto1)) {
                $sql .= " <= '".$pre.$_GET['stor2']."0000' ";
            } else {
                $sql .= " >= '".$pre.$_GET['stor1']."0000' ";
                empty($_GET['stor2']) || $sql .= $sri." <= '".$pre.$_GET['stor2']."0000' ";
            }
        }

        empty($_GET['stor']) || $sql .= "AND `p`.`place` LIKE '%".strtoupper(trim($_GET['stor']))."%' ";
        empty($_GET['sku']) || $sql .= "AND `ps`.`sku` = '".strtoupper(trim($_GET['sku']))."' ";

        return $sql;
    }

    public static function getClient() {
        $client = new PHPRPC_Client();
        $client->useService("http://192.168.5.5:802/profitapi/execute");
        return $client;
    }

    public static function getDatas($sql, $val = NULL, $key = 'sku') {
        //return storsearch::array_columns(json_decode(storsearch::getClient()->getinfo($sql),TRUE), $val, $key);
        $results = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array();
        return storsearch::array_columns($results,$val, $key);
    }

    public static function countStock ($skus, $date = NULL) {
        $skus_str = "'".join("','",$skus)."'";

        $start  = date("Y-m-01", time());
        $end    = empty($date)?date("Y-m-d 23:59:59", time()):$date;

        $sql = "SELECT
                UPPER (`sku`) `sku`,SUM(`warehouse_quantity`) AS `num`
            FROM
                `warehouseorders`
            WHERE
                `warehouse_date` >= '{$start}'
            AND
                `warehouse_date` < '{$end}'
            AND sku IN ({$skus_str})
            GROUP BY `sku`";

        $was = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','num');

        $sql = "SELECT
                 UPPER (`sku`) `sku`,SUM(`quantity`) AS `num`
            FROM
                `deliveryorders`
            WHERE
                `delivery_date` >= '{$start}'
            AND
                `delivery_date` < '{$end}'
            AND sku IN ({$skus_str})
            GROUP BY `sku`";
        $des = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','num');

        $sql = "SELECT
                UPPER (`sku`) `sku`,`quantity` AS `num`
            FROM
                `inventories`
            WHERE
                `inventory_date` = '{$start}'
            AND sku IN ({$skus_str})
            GROUP BY `sku`";

        $ins = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku','num');

        $actuals = array();
        foreach($skus as $sku)
        {
            $in = isset($ins[$sku])?$ins[$sku]:0;
            $wa = isset($was[$sku])?$was[$sku]:0;
            $de = isset($des[$sku])?$des[$sku]:0;

            $actuals[$sku] = $in + $wa - $de;
        }

        unset($was, $des, $ins, $skus, $skus_str, $sql);

        return $actuals;
    }

    public static function array_columns($input,$columnKey,$indexKey = NULL)
    {
        $columnKeyIsNumber = (is_numeric($columnKey))?TRUE:FALSE;
        $indexKeyIsNull    = (is_null($indexKey))?TRUE:FALSE;
        $indexKeyIsNumber  = (is_numeric($indexKey))?TRUE:FALSE;
        $result            = array();

        foreach((array)$input AS $key => $row){
            if($columnKeyIsNumber){
                $tmp = array_slice($row,$columnKey,1);
                $tmp = (is_array($tmp)&&!empty($tmp))?current($tmp):NULL;
            }else{
                $tmp = isset($row[$columnKey])?$row[$columnKey]:NULL;
            }
            if(!$indexKeyIsNull){
                if($indexKeyIsNumber){
                    $key = array_slice($row,$indexKey,1);
                    $key = (is_array($key)&&!empty($key))?current($key):NULL;
                    $key = is_null($key)?0:$key;
                }else{
                    $key = isset($row[$indexKey])?$row[$indexKey]:0;
                }
            }

            $result[$key] = $tmp;
        }

        return $result;
    }
}